"""
从Excel文件中读取数据写入数据库 - 不使用批处理
"""
import openpyxl
import pymysql

conn = pymysql.connect(host='10.7.174.103', port=3306,
                       user='guest', password='Guest.618',
                       database='stock', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
        ws = wb.active
        for row_idx in range(2, ws.max_row + 1):
            values = []
            for col_idx in range(1, ws.max_column):
                values.append(ws.cell(row_idx, col_idx).value)
            cursor.execute(
                'insert into tb_baba_stock '
                '   (trade_date, high_price, low_price, open_price, close_price, trade_volume) '
                'values '
                '   (%s, %s, %s, %s, %s, %s)',
                values
            )
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()
